#!/usr/bin/env python3
"""
SQL文件验证脚本
用于验证数据库模式文件的语法正确性
"""

import os
import re

def validate_sql_file(filepath):
    """验证SQL文件"""
    print(f"验证SQL文件: {filepath}")
    
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # 检查基本语法
    errors = []
    
    # 检查是否有未闭合的引号
    if content.count("'") % 2 != 0:
        errors.append("存在未闭合的单引号")
    
    if content.count('"') % 2 != 0:
        errors.append("存在未闭合的双引号")
    
    # 检查表创建语句
    create_table_matches = re.findall(r'CREATE TABLE[^;]+;', content, re.DOTALL | re.IGNORECASE)
    print(f"找到 {len(create_table_matches)} 个CREATE TABLE语句")
    
    # 检查DROP TABLE语句
    drop_table_matches = re.findall(r'DROP TABLE[^;]+;', content, re.DOTALL | re.IGNORECASE)
    print(f"找到 {len(drop_table_matches)} 个DROP TABLE语句")
    
    # 检查INSERT语句
    insert_matches = re.findall(r'INSERT INTO[^;]+;', content, re.DOTALL | re.IGNORECASE)
    print(f"找到 {len(insert_matches)} 个INSERT语句")
    
    # 检查存储过程
    create_procedure_matches = re.findall(r'CREATE PROCEDURE[^;]+;', content, re.DOTALL | re.IGNORECASE)
    # 正确识别DROP PROCEDURE语句，包括可能的IF EXISTS子句
    drop_procedure_matches = re.findall(r'DROP PROCEDURE[^;]+;', content, re.DOTALL | re.IGNORECASE)
    print(f"找到 {len(create_procedure_matches)} 个CREATE PROCEDURE语句")
    print(f"找到 {len(drop_procedure_matches)} 个DROP PROCEDURE语句")
    
    # 检查是否有语法错误明显的模式
    if 'CREATE TABLE' in content and 'PRIMARY KEY' not in content:
        errors.append("CREATE TABLE语句中缺少PRIMARY KEY")
    
    # 检查INSERT语句的值是否匹配字段数量
    for match in insert_matches:
        # 简单检查INSERT语句格式
        if 'VALUES' not in match and '--' not in match:  # 忽略注释行
            errors.append(f"INSERT语句格式错误: {match[:50]}...")
    
    if errors:
        print("发现以下问题:")
        for error in errors:
            print(f"  - {error}")
        return False
    else:
        print("✅ SQL文件语法检查通过")
        return True

def count_records(filepath):
    """统计各表的记录数"""
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # 统计INSERT语句中的记录数
    insert_matches = re.findall(r'INSERT INTO[^;]+;', content, re.DOTALL | re.IGNORECASE)
    
    table_counts = {}
    for match in insert_matches:
        # 跳过注释行
        if '--' in match:
            continue
            
        # 提取表名
        table_match = re.search(r'INSERT INTO\s+`?(\w+)`?', match, re.IGNORECASE)
        if table_match:
            table_name = table_match.group(1)
            # 统计VALUES中的记录数
            values_matches = re.findall(r'\([^)]+\)', match)
            count = len(values_matches)
            if table_name in table_counts:
                table_counts[table_name] += count
            else:
                table_counts[table_name] = count
    
    print("\n各表记录数统计:")
    for table, count in table_counts.items():
        print(f"  {table}: {count} 条记录")
    
    return table_counts

def check_drop_create_order(filepath):
    """检查DROP和CREATE语句的顺序"""
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # 提取所有表名
    create_tables = re.findall(r'CREATE TABLE[^`]*`([^`]+)`', content, re.IGNORECASE)
    drop_tables = re.findall(r'DROP TABLE[^`]*`([^`]+)`', content, re.IGNORECASE)
    
    print(f"\n表创建顺序: {create_tables}")
    print(f"表删除顺序: {drop_tables}")
    
    # 检查是否每个CREATE TABLE前都有对应的DROP TABLE
    if len(drop_tables) >= len(create_tables):
        print("✅ 每个CREATE TABLE前都有对应的DROP TABLE语句")
        return True
    else:
        print("⚠️  部分CREATE TABLE语句前缺少对应的DROP TABLE语句")
        return False

def check_insert_order(filepath):
    """检查INSERT语句是否在对应的CREATE TABLE之后"""
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # 提取所有表名
    create_tables = re.findall(r'CREATE TABLE[^`]*`([^`]+)`', content, re.IGNORECASE)
    
    # 检查每个表是否有对应的INSERT语句在其后
    insert_checks = []
    for table in create_tables:
        # 查找表的CREATE TABLE语句位置
        create_pattern = f'CREATE TABLE[^`]*`{table}`'
        create_match = re.search(create_pattern, content, re.IGNORECASE | re.DOTALL)
        
        if create_match:
            # 查找该表的INSERT语句位置
            insert_pattern = f'INSERT INTO[^`]*`{table}`'
            insert_match = re.search(insert_pattern, content[create_match.end():], re.IGNORECASE | re.DOTALL)
            
            if insert_match:
                insert_checks.append(f"✅ 表 {table} 的INSERT语句位于CREATE TABLE之后")
            else:
                insert_checks.append(f"⚠️  表 {table} 缺少INSERT语句或位置不正确")
        else:
            insert_checks.append(f"❌ 未找到表 {table} 的CREATE TABLE语句")
    
    for check in insert_checks:
        print(check)
    
    return all("✅" in check or "⚠️" in check for check in insert_checks)  # 允许缺少INSERT语句的情况

def check_foreign_keys(filepath):
    """检查是否还存在外键约束"""
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # 查找外键约束
    foreign_keys = re.findall(r'FOREIGN KEY[^;]*;', content, re.IGNORECASE | re.DOTALL)
    
    if foreign_keys:
        print(f"\n⚠️  发现 {len(foreign_keys)} 个外键约束:")
        for fk in foreign_keys:
            print(f"  {fk.strip()}")
        return False
    else:
        print("\n✅ 已成功移除所有外键约束")
        return True

def check_procedure_order(filepath):
    """检查存储过程的DROP和CREATE顺序"""
    with open(filepath, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # 提取所有存储过程名
    create_procedures = re.findall(r'CREATE PROCEDURE\s+`([^`]+)`', content, re.IGNORECASE)
    drop_procedures = re.findall(r'DROP PROCEDURE[^`]*`([^`]+)`', content, re.IGNORECASE)
    
    print(f"\n存储过程创建顺序: {create_procedures}")
    print(f"存储过程删除顺序: {drop_procedures}")
    
    # 检查是否每个CREATE PROCEDURE前都有对应的DROP PROCEDURE
    if len(drop_procedures) >= len(create_procedures):
        print("✅ 每个CREATE PROCEDURE前都有对应的DROP PROCEDURE语句")
        return True
    else:
        print("⚠️  部分CREATE PROCEDURE语句前缺少对应的DROP PROCEDURE语句")
        return False

if __name__ == "__main__":
    print("开始验证数据库模式文件...")
    
    schema_file = '../migrations/database_schema.sql'
    if os.path.exists(schema_file):
        is_valid = validate_sql_file(schema_file)
        if is_valid:
            count_records(schema_file)
            check_drop_create_order(schema_file)
            check_insert_order(schema_file)
            check_foreign_keys(schema_file)
            check_procedure_order(schema_file)
        else:
            print("❌ SQL文件验证失败")
    else:
        print(f"❌ 文件不存在: {schema_file}")